<?php

include_once(__DIR__ . "/RemoteObject.php");
include_once(__DIR__ . "/setting.php");

class Codium extends RemoteObject
{
	function __construct()
	{
		parent::__construct();
		
		$this->db = null;
	}
	
	function getDb()
	{
		if ($this->db == null)
		{
			global $dbHost;
			global $dbUser;
			global $dbPass;
			global $dbName;
			
			$this->db = @mysql_connect($dbHost, $dbUser, $dbPass);
			mysql_select_db($dbName, $this->db);
		}
		
		return $this->db;
	}
	
	function login($userId, $pass)
	{
		$result = -1;
		$db = $this->getDb();
		
		$dbRet = mysql_query("select * from cod_user where userId = '$userId'", $db);
		
		if ($dbRet)
		{
			$dbRow = mysql_fetch_assoc($dbRet);
			
			if ($dbRow)
			{
				if ($pass == $dbRow["password"])
				{
					$dbRow["password"] = "";
					
					$result = $dbRow;
				}
				else
					$result = -3; //wrong password
			}
			else
				$result = -2; //user not found
		}
		else
			$result = -1; // system error
		
		return $result;
	}
	
	function addMessage($projectId, $userId, $title, $msg)
	{
		$db = $this->getDb();
		
		$dt = date("Y-m-d H:i:s");
		
		mysql_query("insert into cod_message (projectId, messageDate, messageUserId, messageTitle, description) values ($projectId, '$dt', '$userId', '" . mysql_real_escape_string($title) . "', '" . mysql_real_escape_string($msg) . "')", $db);
		
		return true;
	}
	
	function listMessage($projectId)
	{
		$result = array();
		
		$db = $this->getDb();
		
		$sql = "select a.projectId, a.messageDate, a.messageUserId, a.messageTitle, a.description, b.userName as messageUserName from cod_message a " .
				"left outer join cod_user b on a.messageUserId = b.userId " .
				"where a.projectId = $projectId order by messageDate desc";
		
		$dbRet = mysql_query($sql, $db);
		
		if ($dbRet)
		{
			$dbRow = mysql_fetch_assoc($dbRet);
			
			while ($dbRow !== FALSE)
			{
				array_push($result, $dbRow);
				
				$dbRow = mysql_fetch_assoc($dbRet);
			}
		}
		
		return $result;
	}
	
	function listPeople($projectId)
	{
		$result = array();
		
		$db = $this->getDb();
		
		$sql = "select * from cod_user order by userName asc";
		
		$dbRet = mysql_query($sql, $db);
		
		if ($dbRet)
		{
			$dbRow = mysql_fetch_assoc($dbRet);
			
			while ($dbRow !== FALSE)
			{
				array_push($result, $dbRow);
				$dbRow = mysql_fetch_assoc($dbRet);
			}
		}
		
		return $result;
	}
	
	function addTicket($projectId, $userId, $title, $msg, $assignee, $piority, $duedate)
	{
		$db = $this->getDb();
		
		$max = 1;
		
		$sql = "select max(no) as maxNo from cod_ticket where projectId = $projectId";
		
		$dbRet = mysql_query($sql, $db);
		
		if ($dbRet)
		{
			$dbRow = mysql_fetch_assoc($dbRet);
			
			if ($dbRow)
				$max = $dbRow["maxNo"] + 1;
		}
		
		$dt = date("Y-m-d H:i:s");
		$sql = "insert into cod_ticket (projectId, milestoneId, createDate, createUserId, assigneeUserId, ticketTitle, description, state, priorityId, dueDate, resolution, no, rejectReason) " .
				"values ($projectId, 0, '$dt', '$userId', '$assignee', '" . mysql_real_escape_string($title) . "', '" . mysql_real_escape_string($msg) . "', 1, '$piority', '$duedate', '', $max, '')";
		
		mysql_query($sql, $db);
		
		return true;
	}
	
	function ticketChangeState($id, $newState, $msg)
	{
		$db = $this->getDb();
		
		$sql = "update cod_ticket set state = $newState where id = $id";
		mysql_query($sql, $db);
		
		if ($newState == 4) // rejected or
		{
			$sql = "update cod_ticket set assigneeUserId = createUserId, rejectReason = '" . mysql_real_escape_string($msg) . "' where id = $id";
			mysql_query($sql, $db);
		}
		else if ($newState == 5) // conpleted
		{
			$sql = "update cod_ticket set assigneeUserId = createUserId, resolution = '" . mysql_real_escape_string($msg) . "' where id = $id";
			mysql_query($sql, $db);
		}
		
		return $newState;
	}
	
	function listTicket($projectId)
	{
		$result = array();
		
		$db = $this->getDb();
		
		$sql = "select a.id, a.projectId, a.milestoneId, a.createDate, a.createUserId, b.userName as createUserName, a.assigneeUserId, c.userName as assigneeUserName, a.ticketTitle, a.description, a.state, a.priorityId, a.dueDate, a.resolution, a.rejectReason, a.no from cod_ticket a " .
				"left outer join cod_user b on a.createUserId = b.userId " .
				"left outer join cod_user c on a.assigneeUserId = c.userId " .
				"where a.projectId = $projectId order by no asc";
		
		$dbRet = mysql_query($sql, $db);
		
		if ($dbRet)
		{
			$dbRow = mysql_fetch_assoc($dbRet);
			
			while ($dbRow !== FALSE)
			{
				array_push($result, $dbRow);
				
				$dbRow = mysql_fetch_assoc($dbRet);
			}
		}
		
		return $result;
	}
	
	function listTicketAttachment($ticketId)
	{
		$result = array();
		
		$db = $this->getDb();
		
		$sql = "select a.id, a.ticketId, a.fileName, a.userId, b.userName as userName, a.uploadTime, a.state, a.description from cod_ticketattachment a " .
				"left outer join cod_user b on a.userId = b.userId " .
				"where a.ticketId = $ticketId order by uploadTime asc";
		
		$dbRet = mysql_query($sql, $db);
		
		if ($dbRet)
		{
			$dbRow = mysql_fetch_assoc($dbRet);
			
			while ($dbRow !== FALSE)
			{
				array_push($result, $dbRow);
				
				$dbRow = mysql_fetch_assoc($dbRet);
			}
		}
		
		return $result;
	}
	
	function listFile($projectId)
	{
		$result = array();
		
		$db = $this->getDb();
		
		$sql = "select a.id, a.projectId, a.uploadDate, a.uploadUserId, b.userName as uploadUserName, a.fileName, a.description from cod_file a " .
				"left outer join cod_user b on a.uploadUserId = b.userId " .
				"where a.projectId = $projectId order by uploadDate asc";
		
		$dbRet = mysql_query($sql, $db);
		
		if ($dbRet)
		{
			$dbRow = mysql_fetch_assoc($dbRet);
			
			while ($dbRow !== FALSE)
			{
				array_push($result, $dbRow);
				
				$dbRow = mysql_fetch_assoc($dbRet);
			}
		}
		
		return $result;
	}
	
	function addUser($userId, $userName, $pass, $email, $role)
	{
		$db = $this->getDb();
		
		$sql = "insert into cod_user (userId, password, roleId, userName, email) " .
				"values ('" . mysql_real_escape_string($userId) . "', '" . mysql_real_escape_string($pass) . "', $role, '" . mysql_real_escape_string($userName) . "', '" . mysql_real_escape_string($email) . "')";
		
		mysql_query($sql, $db);
		
		return true;
	}
	
	function updateUser($userId, $userName, $pass, $email, $role)
	{
		$db = $this->getDb();
		
		$sql = "update cod_user set userName = '" . mysql_real_escape_string($userName) . "', " .
									"password = '" . mysql_real_escape_string($pass) . "', " .
									"roleId = $role, " .
									"email = '" . mysql_real_escape_string($email) . "' " .
								"where userId = '" . mysql_real_escape_string($userId) . "'";
		
		mysql_query($sql, $db);
		
		return true;
	}
	
	function delUser($userId)
	{
		$db = $this->getDb();
		
		$sql = "delete from cod_user where userId = '" . mysql_real_escape_string($userId) . "'";
		
		mysql_query($sql, $db);
		
		return true;
	}
	
	function listUser()
	{
		$result = array();
		
		$db = $this->getDb();
		
		$sql = "select * from cod_user order by userName asc";
		
		$dbRet = mysql_query($sql, $db);
		
		if ($dbRet)
		{
			$dbRow = mysql_fetch_assoc($dbRet);
			
			while ($dbRow !== FALSE)
			{
				array_push($result, $dbRow);
				$dbRow = mysql_fetch_assoc($dbRet);
			}
		}
		
		return $result;
	}
}

$obj = new Codium();
$obj->dispatch();

?>